﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using Maticsoft.DBUtility;
using DQYT.HTDZ.Model;
using System.Data.OracleClient;

namespace DQYT.HTDZ.OracleDAL
{
    public class TB_Template_Menu_DAL
    {
        /// <summary>
        /// 根据根节点查询目录树
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public DataTable QueryTree(int parentId)
        {
            DataTable itmTree = null;

            itmTree = DbHelperOra.Query_Table(
                string.Format(@"SELECT TMP_ID, TMP_MC, TMP_FID, TMP_SX, TMP_MS, TMP_URL, TMP_ZT, TYP_CODE
                                  FROM TB_TEMPLATE_MENU
                                CONNECT BY PRIOR TMP_ID = TMP_FID
                                 START WITH TMP_ID = {0}  order by tmp_sx", parentId));

            return itmTree;
        }

        /// <summary>
        /// 查询记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable QueryItem(int id)
        {
            DataTable itmTree = null;

            itmTree = DbHelperOra.Query_Table(
                string.Format(@"SELECT TMP_ID, TMP_MC, TMP_FID, TMP_SX, TMP_MS, TMP_URL, TMP_ZT, TYP_CODE
                                  FROM TB_TEMPLATE_MENU
                                WHERE TMP_ID = {0}", id));

            return itmTree;
        }


        /// <summary>
        /// 查询记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable QueryItemByUrl(string url)
        {
            DataTable itmTree = null;

            itmTree = DbHelperOra.Query_Table(
                string.Format(@"SELECT TMP_ID, TMP_MC, TMP_FID, TMP_SX, TMP_MS, TMP_URL, TMP_ZT, TYP_CODE
                                  FROM TB_TEMPLATE_MENU
                                WHERE TMP_URL = '{0}'", url));

            return itmTree;
        }


        public void DeleteItem(int id)
        {
            
            DbHelperOra.ExecuteSql(string.Concat("delete from tb_template_menu where tmp_id in (select a.tmp_id from tb_template_menu a connect by prior tmp_id = tmp_fid start with a.tmp_id =  ", id, ")"));
            
        }

        public bool AddItem(TB_Template_Menu_Model model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tb_template_menu(");
            strSql.Append("TMP_ID,TMP_MC,TMP_FID, TMP_URL, TMP_ZT, TYP_CODE)");
            strSql.Append(" values (");
            strSql.Append("sk_id.nextval,:TMP_MC,:TMP_FID,:TMP_URL,1, :TYP_CODE)");
            OracleParameter[] parameters = {
					new OracleParameter(":TMP_MC", OracleType.NVarChar),
					new OracleParameter(":TMP_FID", OracleType.Int32),
					new OracleParameter(":TMP_URL", OracleType.NVarChar),
                    new OracleParameter(":TYP_CODE", OracleType.Int32)};
            parameters[0].Value = model.TMP_MC;
            parameters[1].Value = model._parentId;
            parameters[2].Value = model.TMP_URL;
            parameters[3].Value = model.TYP_CODE;

            int rows = DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
